"""
Case Type   : 基础功能
Case Name   : 同步基础数据--货币，bool，字符类型，表类型为segment
Create At   : 2022/11/09
Owner       : songjing20
Description :
    1.两个集群创建表,货币,bool,字符类型
    2.发布端插入基础数据
    3.创建发布端
    4.创建订阅
    5.查询订阅端表数据
    6.发布端修改数据
    7.查询订阅端表数据
    8.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.数据同步
    6.成功
    7.数据同步
    8.成功
History     :
"""

import os
import time
import unittest

from yat.test import macro
from yat.test import Node
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant

Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 > Primary_SH.get_node_num(), '非1+2以上环境不执行')
class Pubsubclass(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_userdb_pub = Node(node='PrimaryDbUser')
        self.pri_userdb_sub = Node(node='remote1_PrimaryDbUser')
        self.constant = Constant()
        self.commsh_pub = CommonSH('PrimaryDbUser')
        self.commsh_sub = CommonSH('remote1_PrimaryDbUser')
        self.com_pub = Common()
        self.tb_name = 't_pubsub_0224'
        self.pubname = "pub_pubsub_0224"
        self.subname = "sub_pubsub_0224"
        self.port = str(int(self.pri_userdb_pub.db_port) + 1)
        self.user_param_pub = f'-U {self.pri_userdb_pub.db_user} ' \
                              f'-W {self.pri_userdb_pub.db_password}'
        self.user_param_sub = f'-U {self.pri_userdb_sub.db_user} ' \
                              f'-W {self.pri_userdb_sub.db_password}'
        self.wal_level = self.com_pub.show_param("wal_level")
        text = '-----查询集群状态，配置白名单，发布端设置wal_level=logical-----'
        self.log.info(text)
        self.log.info('-----发布端状态-----')
        status = self.commsh_pub.get_db_cluster_status('detail')
        self.log.info(status)
        self.assertEqual(status.count('Normal'), 4, f'执行失败:{text}')
        self.log.info('-----订阅端状态-----')
        status = self.commsh_sub.get_db_cluster_status(
            'status', env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertTrue(status, f'执行失败:{text}')
        guc_res = self.commsh_pub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, '',
            f'host    replication  {self.pri_userdb_sub.db_user}  '
            f'{self.pri_userdb_sub.db_host}/32  sha256')
        self.log.info(guc_res)
        self.assertTrue(guc_res, f'发布端配置白名单失败:{text}')
        if 'logical' != self.wal_level:
            result = self.commsh_pub.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG, 'wal_level=logical')
            self.assertTrue(result, f'设置wal_level失败:{text}')
            restart_res = self.commsh_pub.restart_db_cluster()
            self.assertTrue(restart_res, f'重启数据库失败:{text}')
        guc_res = self.commsh_sub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host    replication  {self.pri_userdb_pub.db_user} '
            f'{self.pri_userdb_pub.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, f'订阅端配置白名单失败:{text}')

    def test_pubsub(self):
        text1 = '-----step1:两个集群创建表,货币,bool,字符类型;expect:成功-----'
        self.log.info(text1)
        create_tb = f"drop table if exists {self.tb_name} cascade;" \
                    f"create table {self.tb_name}(" \
                    f"i1 int, i2 money primary key, i3 boolean, " \
                    f"i4 char(4), i5 varchar2(5), i6 nvarchar2(6), i7 text," \
                    f"i8 name, i9 \"char\", i10 character(4), i11 nchar(4)," \
                    f"i12 character varying(5), i13 clob, i14 varchar(4))" \
                    f"with (segment=on);"
        pub_res = self.commsh_pub.execut_db_sql(
            create_tb, sql_type=self.user_param_pub)
        self.log.info(pub_res)
        self.assertEqual(pub_res.count(self.constant.TABLE_CREATE_SUCCESS),
                         2, f'发布端建表失败:{text1}')
        sub_res = self.commsh_sub.execut_db_sql(
            create_tb, self.user_param_sub, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(sub_res)
        self.assertEqual(sub_res.count(self.constant.TABLE_CREATE_SUCCESS),
                         2, f'订阅端建表失败:{text1}')

        text2 = '-----step2:发布端插入基础数据;expect:成功'
        self.log.info(text2)
        insert_res = self.commsh_pub.execut_db_sql(
            f"insert into {self.tb_name} values("
            f"1,567,TRUE,'ok','good','longw','文本类型数据','jjjjjjjjj',"
            f"'p','my','lw','well','数据','yo');", self.user_param_pub)
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      f'执行失败:{text2}')

        text3 = '-----step3:创建发布端;expect:成功-----'
        self.log.info(text3)
        pub_res = self.commsh_pub.execut_db_sql(
            f"create publication {self.pubname} for all tables;",
            sql_type=self.user_param_pub)
        self.log.info(pub_res)
        self.assertIn(self.constant.create_pub_succ_msg, pub_res,
                      f'执行失败:{text3}')

        text4 = '-----step4:创建订阅端;expect:成功-----'
        self.log.info(text4)
        guc_res = self.commsh_sub.execute_generate(
            macro.COMMON_PASSWD, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertIn(self.constant.create_keycipher_success, guc_res,
                      f'执行失败:{text4}')
        create_sub = f"""drop subscription if exists {self.subname};
        create subscription {self.subname} connection 
        'host={self.pri_userdb_pub.db_host} port={self.port} 
        user={self.pri_userdb_pub.db_user} dbname={self.pri_userdb_pub.db_name}
        password={self.pri_userdb_pub.ssh_password}' 
        publication {self.pubname} with (binary=false,copy_data=true);"""
        sub_res = self.commsh_sub.execut_db_sql(
            create_sub, self.user_param_sub, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(sub_res)
        self.assertIn(self.constant.create_sub_succ_msg,
                      sub_res, f'执行失败:{text4}')

        text5 = '-----step5:查询订阅端数据;expect:数据同步-----'
        self.log.info(text5)
        time.sleep(6)
        sql = f"select * from {self.tb_name};"
        sub_res = self.commsh_sub.execut_db_sql(
            sql, self.user_param_sub, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(sub_res)
        self.assertIn('1 | $567.00 | t  | ok   | good | longw '
                      '| 文本类型数据 | jjjjjjjjj | p  | my   | lw   '
                      '| well | 数据 | yo', sub_res, f'执行失败{text5}')

        text6 = '-----step6:发布端修改数据;expect:成功-----'
        self.log.info(text6)
        delete_res = self.commsh_pub.execut_db_sql(
            f"delete from {self.tb_name};", self.user_param_pub)
        self.log.info(delete_res)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, delete_res,
                      f'执行失败:{text6}')

        text7 = '-----step7:查询订阅端数据;expect:数据同步-----'
        self.log.info(text7)
        sub_res = self.commsh_sub.execut_db_sql(
            sql, self.user_param_sub, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(sub_res)
        self.assertIn('0 rows', sub_res, f'执行失败:{text7}')

    def tearDown(self):
        text8 = '-----step8:清理环境 expect: 成功-----'
        self.log.info(text8)
        drop_sub_res = self.commsh_sub.execut_db_sql(
            f"drop subscription {self.subname}", self.user_param_sub,
            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_sub_res)
        drop_pub_res = self.commsh_pub.execut_db_sql(
            f"DROP PUBLICATION {self.pubname};", self.user_param_pub)
        self.log.info(drop_pub_res)
        drop_tb = f'drop table {self.tb_name};'
        pub_res = self.commsh_pub.execut_db_sql(drop_tb, self.user_param_pub)
        self.log.info(pub_res)
        sub_res = self.commsh_sub.execut_db_sql(
            drop_tb, self.user_param_sub, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(sub_res)
        wal_level_res = self.commsh_pub.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'wal_level={self.wal_level}')
        self.log.info(wal_level_res)
        pub_guc_res = self.commsh_pub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, '',
            f'host  replication  {self.pri_userdb_sub.db_user}  '
            f'{self.pri_userdb_sub.db_host}/32')
        self.log.info(pub_guc_res)
        restart_res = self.commsh_pub.restart_db_cluster()
        sub_guc_res = self.commsh_sub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  replication  {self.pri_userdb_pub.db_user}  '
            f'{self.pri_userdb_pub.db_host}/32', macro.DB_ENV_PATH_REMOTE1)
        self.log.info(sub_guc_res)
        self.assertTrue(self.constant.drop_sub_succ_msg in drop_sub_res,
                        f'删除订阅失败:{text8}')
        self.assertIn(self.constant.drop_pub_succ_msg, drop_pub_res,
                      f'删除发布失败:{text8}')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, pub_res,
                      f'删除订阅端表失败:{text8}')
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sub_res,
                      f'删除发布端表失败:{text8}')
        self.assertTrue(wal_level_res, f'执行失败:{text8}')
        self.assertTrue(restart_res, f'发布端重启数据库失败:{text8}')
        self.assertTrue(pub_guc_res, f'发布端删除白名单失败:{text8}')
        self.assertTrue(sub_guc_res, f'订阅端删除白名单失败:{text8}')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
